﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace hrainwebbase.basecs
{
    public class ExcelInOut
    {

        public static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheetNames = sheetName.Split(',');
            for (int i = 0; i < sheetNames.Length; i++)
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);            // handling header.           
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);            // handling value.           
                int rowIndex = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }
            }
            workbook.Write(ms); ms.Flush();
            ms.Position = 0;
            workbook = null;
            return ms;
        }
        /// <summary>    /// 由DataSet导出Excel    /// </summary>    /// <param name="sourceTable">要导出数据的DataTable</param>    /// <param name="fileName">指定Excel工作表名称</param>    /// <returns>Excel工作表</returns>    public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)    {        MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);         HttpContext.Current.Response.BinaryWrite(ms.ToArray());        HttpContext.Current.Response.End();        ms.Close();        ms = null;    }
        private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);        // handling header.      
            foreach (DataColumn column in sourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);        // handling value.       
            int rowIndex = 1; foreach (DataRow row in sourceTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }    /// <summary>   
        /// /// 由DataTable导出Excel    /// </summary>    /// <param name="sourceTable">要导出数据的DataTable</param>    /// <param name="fileName">指定Excel工作表名称</param>    /// <returns>Excel工作表</returns>    public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName)    {        MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName) as MemoryStream;        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);         HttpContext.Current.Response.BinaryWrite(ms.ToArray());        HttpContext.Current.Response.End();        ms.Close();        ms = null;    }
        /// <summary>
        ///toExcel 的摘要说明
        /// </summary>
        public static Stream ExportTotalDataTableToExcel(DataTable sourceTable1, DataTable sourceTable2, string sheetName, string strHeaderText, string footText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
            if (sourceTable2 != null) //set the sourceTable2's column width.
            {
                sheet.SetColumnWidth(1, 20 * 256);
                sheet.SetColumnWidth(2, 15 * 256);
                sheet.SetColumnWidth(3, 30 * 256);
            }
            HSSFRow header = (HSSFRow)sheet.CreateRow(0);
            header.HeightInPoints = 25;
            header.CreateCell(0).SetCellValue(strHeaderText);

            //set the title of this sheet table.
            HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFFont font = (HSSFFont)workbook.CreateFont();
            font.FontHeightInPoints = 13;
            font.Boldweight = 200;
            headStyle.SetFont(font);
            header.GetCell(0).CellStyle = headStyle;

            //set the header column style of the sheet.
            HSSFCellStyle styleBorder = (HSSFCellStyle)workbook.CreateCellStyle();
            styleBorder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            styleBorder.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            styleBorder.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            styleBorder.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            styleBorder.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            styleBorder.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            HSSFFont fieldfont = (HSSFFont)workbook.CreateFont();
            fieldfont.FontHeightInPoints = 10;
            fieldfont.Boldweight = 100;
            styleBorder.SetFont(fieldfont);

            //set the column's style expect the header column row.
            HSSFCellStyle columnStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            columnStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            columnStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            columnStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            columnStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            columnStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            columnStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            columnStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            columnStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            columnStyle.SetFont(fieldfont);

            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);        // handling header.      
            foreach (DataColumn column in sourceTable1.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                headerRow.GetCell(column.Ordinal).CellStyle = styleBorder;

            }
            int rowIndex = 2;
            foreach (DataRow row in sourceTable1.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable1.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    dataRow.GetCell(column.Ordinal).CellStyle = columnStyle;
                }
                rowIndex++;
            }
            int extraRowIndex = rowIndex++;
            if (sourceTable2 != null)
            {

                HSSFRow dataRow1 = (HSSFRow)sheet.CreateRow(extraRowIndex);

                foreach (DataColumn column in sourceTable1.Columns)
                {
                    if (column.Ordinal == 0)
                    {
                        dataRow1.CreateCell(0).SetCellValue("汇总:");
                    }
                    else
                    {
                        dataRow1.CreateCell(column.Ordinal).SetCellValue("");
                    }
                    dataRow1.GetCell(column.Ordinal).CellStyle = columnStyle;
                }
                extraRowIndex++;
                foreach (DataRow row in sourceTable2.Rows)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(extraRowIndex);

                    foreach (DataColumn column in sourceTable1.Columns)
                    {
                        if (column.Ordinal == 0)
                        {
                            dataRow.CreateCell(0).SetCellValue(row[0].ToString() + ":" + (row[1] == null ? "0" : row[1].ToString()) + "个");
                        }
                        else
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue("");
                        }
                        dataRow.GetCell(column.Ordinal).CellStyle = columnStyle;
                    }
                    extraRowIndex++;
                }

                HSSFRow lastRow = (HSSFRow)sheet.CreateRow(extraRowIndex);
                foreach (DataColumn column in sourceTable1.Columns)
                {
                    if (column.Ordinal == 0)
                    {
                        lastRow.CreateCell(0).SetCellValue("合计:" + sourceTable1.Rows.Count + "个");
                    }
                    else
                    {
                        lastRow.CreateCell(column.Ordinal).SetCellValue("");
                    }
                    lastRow.GetCell(column.Ordinal).CellStyle = columnStyle;

                    //foreach (DataColumn column in sourceTable2.Columns)
                    //{
                    //    lastRow.GetCell(column.Ordinal).CellStyle = styleBorder;
                    //}
                }
                extraRowIndex++;
            }
            extraRowIndex = extraRowIndex + 1;
            HSSFRow lastTotalRow = (HSSFRow)sheet.CreateRow(extraRowIndex);
            lastTotalRow.CreateCell(0).SetCellValue(footText);
            lastTotalRow.GetCell(0).CellStyle = headStyle;

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }
        /// <summary>    /// 由DataTable导出Excel    /// </summary>    /// <param name="sourceTable">要导出数据的DataTable</param>    /// <param name="fileName">指定Excel工作表名称</param>    /// <returns>Excel工作表</returns>    public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName)    {        MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName) as MemoryStream;        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);         HttpContext.Current.Response.BinaryWrite(ms.ToArray());        HttpContext.Current.Response.End();        ms.Close();        ms = null;    }


        public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName)
        {
            MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName) as MemoryStream;
            if (ms != null)
            {
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                HttpContext.Current.Response.End();
                ms.Close();
            }
            ms = null;
        }
        public static void ExportTotalDataTableToExcel(DataTable sourceTable, DataTable sourceTable1, string fileName, string sheetName, string strHeaderText, string footText)
        {
            MemoryStream ms = ExportTotalDataTableToExcel(sourceTable, sourceTable1, sheetName, strHeaderText, footText) as MemoryStream;
            if (ms != null)
            {
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                HttpContext.Current.Response.End(); ms.Close();
            }
            ms = null;
        }



        /// <summary>
        /// 可定制列宽度，请设置columsLen参数
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <param name="columsLen"></param>
        public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName, params int[] columsLen)
        {
            MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName, columsLen) as MemoryStream;
            if (ms != null)
            {
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                HttpContext.Current.Response.End(); ms.Close();
            }
            ms = null;
        }

        private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName, params int[] columsLen)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
            HSSFCell cell;
            #region cellStyle
            HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            HSSFFont font = (HSSFFont)workbook.CreateFont();
            font.FontName = "仿宋";//字体
            font.FontHeightInPoints = 14;//字体大小：14
            cellStyle.SetFont(font);
            #endregion

            #region headerCellStyle
            HSSFCellStyle headerCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            headerCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            HSSFFont font2 = (HSSFFont)workbook.CreateFont();
            font2.FontName = "黑体";//字体
            font2.FontHeightInPoints = 14;//字体大小：22
            headerCellStyle.SetFont(font2);
            #endregion

            foreach (DataColumn column in sourceTable.Columns)
            {
                cell = (HSSFCell)headerRow.CreateCell(column.Ordinal);
                cell.SetCellValue(column.ColumnName);
                cell.CellStyle = headerCellStyle;

            }
            if (columsLen.Length > 0)
            {
                for (int i = 0; i < columsLen.Length; i++)
                {
                    sheet.SetColumnWidth(i, columsLen[i] * 256);
                }
            }
            int rowIndex = 1; foreach (DataRow row in sourceTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    cell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(row[column].ToString());
                    cell.CellStyle = cellStyle;
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }

        /// <summary>
        /// 可定制列宽度，请设置columsLen参数,设置标题和结尾
        /// </summary>
        /// <param name="source"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetNames">sheet名称</param>
        /// <param name="titleTexts">标题</param>
        /// <param name="footTexts">结尾</param>
        /// <param name="columsLen">宽度</param>
        /// <returns></returns>
        public static void ExportDataSoureToExcel(DataSet source, string fileName, string sheetNames, string headTexts = "", string footTexts = "", params int[] columsLen)
        {
            MemoryStream ms = ExportDataSoureToExcel(source, sheetNames, headTexts, footTexts, columsLen) as MemoryStream;
            if (ms != null)
            {
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                HttpContext.Current.Response.End(); ms.Close();
            }
            ms = null;


        }

        private static Stream ExportDataSoureToExcel(DataSet source, string sheetNames, string titleTexts, string footTexts, params int[] columsLen)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheets = sheetNames.Split(',');
            string[] titles = titleTexts.Split(',');
            string[] foots = footTexts.Split(',');

            #region cellStyle
            HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.WrapText = true;

            HSSFFont font = (HSSFFont)workbook.CreateFont();
            font.FontName = "仿宋";//字体
            font.FontHeightInPoints = 14;//字体大小：14
            cellStyle.SetFont(font);
            #endregion

            #region headerCellStyle
            HSSFCellStyle headerCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            headerCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            headerCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            headerCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            HSSFFont font2 = (HSSFFont)workbook.CreateFont();
            font2.FontName = "黑体";//字体
            font2.FontHeightInPoints = 14;//字体大小：22
            headerCellStyle.SetFont(font2);
            #endregion

            #region headTextStyle
            HSSFCellStyle headTextStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            headTextStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            headTextStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            HSSFFont font3 = (HSSFFont)workbook.CreateFont();
            font3.FontName = "黑体";//字体
            font3.FontHeightInPoints = 18;//字体大小：22
            headTextStyle.SetFont(font3);
            #endregion

            #region footTextStyle
            HSSFCellStyle footTextStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            footTextStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            footTextStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            HSSFFont font4 = (HSSFFont)workbook.CreateFont();
            font4.FontName = "仿宋";//字体
            font4.FontHeightInPoints = 14;//字体大小：22
            footTextStyle.SetFont(font4);
            #endregion

            for (int a = 0; a < source.Tables.Count; a++)
            {
                DataTable sourceTable = source.Tables[a];
                string sheetName = sheets[a];

                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
                HSSFCell cell;
                int rowIndex = 1;
                int columsCount = source.Tables[a].Columns.Count;

                #region title
                if (titles.Length > 0)
                {
                    HSSFRow titleRow = (HSSFRow)sheet.CreateRow(0);
                    cell = (HSSFCell)titleRow.CreateCell(0);
                    cell.SetCellValue(titles[a]);
                    cell.CellStyle = headTextStyle;
                    //合并标题行
                    sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, columsCount - 1));

                    rowIndex++;
                }
                #endregion

                #region headerRow
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex - 1);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    cell = (HSSFCell)headerRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = headerCellStyle;

                }
                if (columsLen.Length > 0)
                {
                    for (int i = 0; i < columsLen.Length; i++)
                    {
                        sheet.SetColumnWidth(i, columsLen[i] * 256);
                    }
                }
                #endregion

                #region dataRow
                foreach (DataRow row in sourceTable.Rows)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        cell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
                        cell.SetCellValue(row[column].ToString());
                        cell.CellStyle = cellStyle;
                    }
                    rowIndex++;
                }
                #endregion

                #region foot
                if (foots.Length > 0)
                {
                    HSSFRow titleRow = (HSSFRow)sheet.CreateRow(rowIndex);
                    cell = (HSSFCell)titleRow.CreateCell(0);
                    cell.SetCellValue(foots[a]);
                    cell.CellStyle = footTextStyle;
                    //合并标题行
                    sheet.AddMergedRegion(new NPOI.SS.Util.Region(rowIndex, 0, rowIndex, columsCount - 1));
                    rowIndex++;
                }
                #endregion

                sheet = null;
                headerRow = null;
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workbook = null;
            return ms;


        }
        /// <summary>
        /// 把Sheet中的数据转换为DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static DataTable GetTable(Stream impFile)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(impFile);
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
            int begin = sheet.FirstRowNum;

            //int end = sheet.LastRowNum;
            DataTable table = new DataTable();
            int end = sheet.PhysicalNumberOfRows;
            //为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
            //为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
            ////默认，第一行是字段
            IRow headRow = sheet.GetRow(0);
            //int colsCount = headRow.PhysicalNumberOfCells;
            int colsCount = headRow.LastCellNum;
            //设置datatable字段
            for (int i = 0, len = headRow.LastCellNum; i < len; i++)
            {
                table.Columns.Add(i.ToString());
            }
            for (int x = 0; x < end; x++)
            {
                IRow row = sheet.GetRow(x);
                if (row == null)
                    break;
                DataRow dr = table.NewRow();
                bool infonull = false;
                for (int y = 0; (y < colsCount && y < (row.LastCellNum)); y++)
                {

                    dr[y] = GetCellValue(row.GetCell(y));
                    if (infonull == false && (!string.IsNullOrEmpty(dr[y].ToString())))
                    { infonull = true; }
                }
                if (infonull) table.Rows.Add(dr);
            }

            sheet = null;
            workbook = null;
            return table;
            ////默认，第一行是字段
            //IRow headRow = sheet.GetRow(0);
            ////设置datatable字段
            //for (int i = headRow.FirstCellNum, len = headRow.LastCellNum; i < len; i++)
            //{
            //    dt.Columns.Add(headRow.Cells[i].StringCellValue);
            //}
            ////遍历数据行
            //for (int i = (sheet.FirstRowNum + 1), len = sheet.LastRowNum; i < len; i++)
            //{
            //    IRow tempRow = sheet.GetRow(i);
            //    DataRow dataRow = dt.NewRow();

            //    //遍历一行的每一个单元格
            //    for (int r = 0, j = tempRow.FirstCellNum, len2 = tempRow.LastCellNum; j < len2; j++, r++)
            //    {

            //        ICell cell = tempRow.GetCell(j);

            //        if (cell != null)
            //        {
            //            dataRow[r] = GetCellValue(cell);

            //        }
            //    }
            //    dt.Rows.Add(dataRow);
            //}
            // return dt;
        }

        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }
    }
}